﻿CREATE PROCEDURE [dbo].[proc_Customer_GetEmployeeList]
(
	@UserName varchar(50)
)
As
Begin
	declare @companyid int,@Days int,@DepartmentId int
	Declare @Extract int,@pDays int,@tDays int	--该部门最大保护天数、最大跟踪天数

	EXEC @companyid=proc_Company_GetID @UserName,@companyid		--该用户的单位ID

--	Select @Extract=DateDiff(dd,'1900-1-1',getdate())	--今天
	--申领了客户但未保护(没写批注)的，次日自动进入公库(不影响下次被申领)	
--	Update Customer Set ProtectName=NULL,ProtectDate=NULL Where ProtectName=@UserName And ProtectId=1 And ExtractDate<@Extract And companyId=@companyid
--2012-03-03 干掉本公司所有人的
--	Update Customer Set ProtectName=NULL,ProtectDate=NULL Where (ProtectName Is Not NULL) And ProtectId=1 And ExtractDate<@Extract And companyId=@companyid

	set @DepartmentId = (Select DepartmentId From Employee Where UserName=@UserName)--部门Id
--	Set @Days = (Select ProtectDays From Department Where id=@DepartmentId)

	Select @pDays=ProtectDays,@tDays=TrackDays From Department Where id=@DepartmentId

	If @pDays Is Null
 		Set @pDays = 0

	If @tDays Is Null
		Set @tDays = 0

	--已保护,但过了保护日期仍未签单的,自动踢入公库并且5日内不能再次被申领(根据UpTime)
--	Update Customer Set ProtectId=1,ProtectName=NULL,ProtectDate=NULL,UpTime=GetDate() Where ProtectName=@UserName And DateDIFF(Day,ProtectDate,GetDate())>@Days And companyId=@companyid

	--提取剩下的客户
	Select 
		Id,
		delFlag,
		CustomerId,
		CustomerName,
		Project,
		ProtectId,
		IsNull((Select Title From Customer_Source Where Id=C.SourceId),'...') As Source,
		IsNull(ProtectDate,'1900-01-01') As ProtectDate,
		IsNull(TrackDate,'1900-01-01') As TrackDate,
		(Case ProtectId When 2 Then @pDays-DateDiff(day,IsNull(ProtectDate,GetDate()),GetDate()) When 4 Then @tDays-DateDiff(day,IsNull(ProtectDate,GetDate()),GetDate()) Else 0 End) As Countdown,
		IsNull((Select Title From Customer_Industry Where Id=C.IndustryOneId),'...') As IndustryOne,
		IsNull((Select Title From Customer_Industry Where Id=C.IndustryTwoId),'...') As IndustryTwo,
		IsNull((Select Title From Customer_Type Where Id=C.TypeId),'...') As Type,
		CreateDate,ZName,Tel
	From Customer C
	Where ProtectName=@UserName And Audit=1 And Companyid=@companyid Order by ProtectDate Desc,ProtectId ASC ,UpTime ASC
End